raw_beds <- read_csv("data/beds_by_nhs_board_of_treatment_and_specialty.csv")

raw_activity <- read_csv("data/inpatient_and_daycase_by_nhs_board_of_treatment_and_specialty.csv")

Cleaning Data

wranged_beds_no_gj <- raw_beds %>% 
  clean_names() %>% 
  mutate(q = str_sub(quarter, -2)) %>% 
  # Change to date format
  mutate(quarter = yq(quarter)) %>% 
  # rename the columns above 
  mutate(date = quarter,
         quarter = q) %>% 
  # Change Heath Board names to read better
  mutate(hb = if_else(hb %in% "S08000015", "Ayrshire & Arran",
                      if_else(hb %in% "S08000016", "Borders",
                      if_else(hb %in% "S08000017", "Dumfries & Galloway",
                      if_else(hb %in% "S08000019", "Forth Valley",
                      if_else(hb %in% "S08000020", "Grampian",
                      if_else(hb %in% "S08000022", "Highland",
                      if_else(hb %in% "S08000024", "Lothian",
                      if_else(hb %in% "S08000025", "Orkney",
                      if_else(hb %in% "S08000026", "Shetland",
                      if_else(hb %in% "S08000028", "Western Isles",
                      if_else(hb %in% "S08000029", "Fife",
                      if_else(hb %in% "S08000030", "Tayside",
                      if_else(hb %in% "S08000031", "Greater Glasgow & Clyde",
                      if_else(hb %in% "S08000032", "Lanarkshire",
                      if_else(hb %in% "S92000003", "Scotland", NA_character_)
                      ))))))))))))))) %>% 
  # take out location names to take out duplicate
  filter(location_qf == "d") %>%
  
  # Take out of cleaning script and add when pulling in shiny
  
  # take out Golden Jubilee
  filter(hb != is.na(hb)) %>%
  # take out unused columns
  # take out location to remove duplicate
  select(date, quarter, hb, specialty_name, all_staffed_beds, 
         total_occupied_beds, average_available_staffed_beds,
         average_occupied_beds, percentage_occupancy)
  
wranged_beds_no_gj
activity_no_gj <- raw_activity %>% 
  clean_names() %>% 
  # Create new column for quarters
  mutate(q = str_sub(quarter, -2)) %>% 
  # Change to date format
  mutate(quarter = yq(quarter)) %>% 
  # rename the columns above 
  mutate(date = quarter,
         quarter = q) %>% 
  # Change Heath Board names to read better
  mutate(
    hb = if_else(hb %in% "S08000015", "Ayrshire & Arran",
                  if_else(hb %in% "S08000016", "Borders",
                  if_else(hb %in% "S08000017", "Dumfries & Galloway",
                  if_else(hb %in% "S08000019", "Forth Valley",
                  if_else(hb %in% "S08000020", "Grampian",
                  if_else(hb %in% "S08000022", "Highland",
                  if_else(hb %in% "S08000024", "Lothian",
                  if_else(hb %in% "S08000025", "Orkney",
                  if_else(hb %in% "S08000026", "Shetland",
                  if_else(hb %in% "S08000028", "Western Isles",
                  if_else(hb %in% "S08000029", "Fife",
                  if_else(hb %in% "S08000030", "Tayside",
                  if_else(hb %in% "S08000031", "Greater Glasgow & Clyde",
                  if_else(hb %in% "S08000032", "Lanarkshire",
                  if_else(hb %in% "S92000003", "Scotland", NA_character_)
                  ))))))))))))))) %>% 
  # take out location names to take out duplicate
  filter(location_qf == "d") %>% 
  
  # Take out of cleaning script and add when pulling in shiny
  
  
  # take out Golden Jubilee
  filter(hb != is.na(hb)) %>%
  # take out unused columns
  select(date, quarter, hb, admission_type, 
         specialty, specialty_name, episodes, 
         length_of_episode, average_length_of_episode, 
         spells, length_of_spell, average_length_of_spell)
  
activity_no_gj 

Exploratory analysis

unique(activity_no_gj$specialty_name)
##  [1] "General Medicine"               "Cardiology"                    
##  [3] "Infectious Diseases"            "Dermatology"                   
##  [5] "Endocrinology & Diabetes"       "Gastroenterology"              
##  [7] "Geriatric Medicine"             "Medical Oncology"              
##  [9] "Renal Medicine"                 "Rehabilitation Medicine"       
## [11] "Respiratory Medicine"           "Rheumatology"                  
## [13] "General Surgery"                "Anaesthetics"                  
## [15] "Ear, Nose & Throat (ENT)"       "Ophthalmology"                 
## [17] "Trauma and Orthopaedic Surgery" "Plastic Surgery"               
## [19] "Urology"                        "Haematology"                   
## [21] "Neurology"                      "Palliative Medicine"           
## [23] "Clinical Oncology"              "Clinical Genetics"             
## [25] "Cardiothoracic Surgery"         "Neurosurgery"                  
## [27] "Allergy"                        "Intensive Care Medicine"       
## [29] "Clinical Radiology"             "Immunology"                    
## [31] "Clinical Neurophysiology"       "Genito-Urinary Medicine"
unique(wranged_beds_no_gj$hb)
##  [1] "Ayrshire & Arran"        "Borders"                
##  [3] "Dumfries & Galloway"     "Fife"                   
##  [5] "Forth Valley"            "Grampian"               
##  [7] "Greater Glasgow & Clyde" "Highland"               
##  [9] "Lanarkshire"             "Lothian"                
## [11] "Orkney"                  "Shetland"               
## [13] "Tayside"                 "Western Isles"          
## [15] "Scotland"


KPI: Capacity – what is happening to the number of beds over the period? Perhaps think about the specialities these are if there has been specific variation?

Shows the percentage occupancy per Specialty grouping

wranged_beds_no_gj %>% 
  filter(str_detect(specialty_name, " Grouping"),
         specialty_name != "Other Grouping",
         hb == "Scotland") %>% 
  mutate(specialty_name = str_sub(specialty_name, start = 1, end = -9)) %>%
  ggplot(aes(x = date, y = percentage_occupancy, col = specialty_name)) +
  geom_point() +
  geom_line()


Obstetrics and Dental look odd as they have 100% occupancy some of the time, some show seasonal peaks.

Can change the head() in the chunk below to filter down the specialty. For shiny can filter by the highest occupancy or the specialty_name Have removed Obstetrics and Dental just to make clearer

bigest_occupancy <- wranged_beds_no_gj %>% 
  filter(str_detect(specialty_name, " Grouping"),
         specialty_name != "Other Grouping",
         specialty_name != "Obstetrics Grouping",
         specialty_name != "Dental Grouping",
         hb == "Scotland") %>% 
  group_by(specialty_name) %>% 
  summarise(count = sum(percentage_occupancy)) %>% 
  arrange(desc(count)) %>% 
  # change head to 
  head(5)

wranged_beds_no_gj %>% 
  filter(specialty_name %in% c(bigest_occupancy$specialty_name)) %>%
  filter(str_detect(specialty_name, " Grouping"),
         specialty_name != "Other Grouping",
         hb == "Scotland") %>% 
  mutate(specialty_name = str_sub(specialty_name, start = 1, end = -9)) %>%
  ggplot(aes(x = date, y = percentage_occupancy, col = specialty_name)) +
  geom_point() +
  geom_line()


This shows the regions

Can use for shiny changing the specialty_name.

Can add try to add a label to show what the highest number is

shows remote areas don’t need/uses some services so have belive have less can over lay the number total_occupied_beds at high point to give scale

wranged_beds_no_gj %>% 
  filter(str_detect(specialty_name, " Grouping"),
         specialty_name != "Other Grouping",
         specialty_name == "Emergency Grouping") %>% 
  mutate(specialty_name = str_sub(specialty_name, start = 1, end = -9)) %>%
  ggplot(aes(x = date, y = percentage_occupancy, col = hb)) +
  geom_point() +
  geom_line() +
  facet_wrap(~ hb) +
  theme(legend.position="none")